﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace CustomControlDatabaseInterface.Utilities
{
    public static class DatabaseUtil
    {

        public static String Server { get; set; }
        public static String Database { get; set; }

        private static MySqlConnection Connection { get; set; }

        public static void SetServerDatabase(String server, String database)
        {
            Server = server;
            Database = database;
        }

        public static void SetConnection(String userName, String password)
        {
            String connectionString = "SERVER=" + Server + "; DATABASE=" + Database + "; UID=" + userName + "; PASSWORD=" + password + ";";
            Connection = new MySqlConnection(connectionString);
        }

        public static Boolean TryOpenConnection(String username, String password)
        {
            String connectionString = "SERVER=" + Server + "; DATABASE=" + Database + "; UID=" + username + "; PASSWORD=" + password + ";";
            try
            {
                Connection = new MySqlConnection(connectionString);
                Connection.Open();
            }
            catch (MySqlException)
            {
                return false;
            }
            catch (Exception)
            {
                return false;
            }
            Connection.Close();
            Connection = null; 
            return true;

        }

        public static List<String> GetTableHeaders(String tableName)
        {
            Connection.Open();
            MySqlCommand command = Connection.CreateCommand();
            String commandText =
                "SELECT Column_Name FROM Information_Schema.Columns WHERE Table_Schema='customcontrolsdatabase' AND Table_Name='" +
                tableName + "';";
            command.CommandText = commandText;
            MySqlDataAdapter adapter = new MySqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            Connection.Close();
            return ConvertColumnToList(table, "column_Name");

        }

        public static List<String> GetTableNames()
        {
            try
            {
                Connection.Open();
            }
            catch (Exception)
            {
            }

            MySqlCommand command = Connection.CreateCommand();
            String commandText = "USE customcontrolsdatabase;SHOW tables";
            command.CommandText = commandText;
            MySqlDataAdapter adapter = new MySqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            Connection.Close();
            return ConvertColumnToList(table, "tables_in_customcontrolsdatabase");
        }

        public static List<String> GetDataRow( String query) {
            Connection.Open();
            MySqlCommand command = Connection.CreateCommand();
            command.CommandText = query; 

            MySqlDataAdapter adapter = new MySqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            Connection.Close();

            if (IsTableRow(table)) {
                return ConvertRowToList(table);     
            }
            return null; 
        }

        public static List<String> GetDataColumn(String query, String columnName)
        {
            Connection.Open();
            MySqlCommand command = Connection.CreateCommand();
            command.CommandText = query;

            MySqlDataAdapter adapter = new MySqlDataAdapter(command);
            DataTable table = new DataTable();
            adapter.Fill(table);
            Connection.Close();

            if (IsTableColumn(table)) {
                return ConvertColumnToList(table, columnName);     
            }
            else throw new Exception("GetDataColumn does not return a column!");
            

        }


        public static void UpdateDataRow( String query) {
            Connection.Open();
            MySqlCommand command = Connection.CreateCommand();
            command.CommandText = query;
            command.ExecuteNonQuery();
            Connection.Close();

        }

        public static void ExecuteNonQuery( String query ) {
            Connection.Open();
            MySqlCommand command = Connection.CreateCommand();
            command.CommandText = query;
            command.ExecuteNonQuery();
            Connection.Close(); 
        }



        #region Private Helpers

        private static List<String> ConvertColumnToList(DataTable table, String header)
        {
            List<String> returnList = new List<String>();
            int columnNumber = table.Columns.IndexOf(header);
            foreach (DataRow row in table.Rows)
            {
                returnList.Add(row.ItemArray[columnNumber].ToString());
            }
            return returnList;
        }

        private static List<String> ConvertRowToList( DataTable table) {
            List<String> returnList = new List<string>();
            int columnCount = table.Columns.Count;
            DataRow row = table.Rows[0]; 
            for (int i = 0; i < columnCount; i++) {
                returnList.Add(row.ItemArray[i].ToString());
            }
            return returnList; 
        }


        private static ObservableCollection<String> ParseColumnToCollection(DataTable table, String header)
        {
            ObservableCollection<String> returnList = new ObservableCollection<string>();
            int columnNumber = table.Columns.IndexOf(header);
            foreach (DataRow row in table.Rows)
            {
                returnList.Add(row.ItemArray[columnNumber].ToString());
            }
            return returnList;
        }

        private static bool IsTableRow( DataTable table ) {
            if (table.Rows.Count < 1) return false;
            return true; 
        }

        private static bool IsTableColumn( DataTable table ) {
            if (table.Columns.Count > 1) return false;
            return true; 
        }

        #endregion




    }
}
